package sql;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;

public class Reportdb {

	public ArrayList<String> getreport(Date start,Date end,String status) throws SQLException
	{
		try 
		{

			Statement stmt;
			Date date1 = null;
			SQLConnection conn = new  SQLConnection();
			stmt = conn.getConnection().createStatement();
			ArrayList<String> ar = new ArrayList<String>();
			ResultSet rs= stmt.executeQuery("SELECT end_date,sum(price) as cnt FROM mtl.supplier_orders where Start_date >= '"+start+"' AND end_date  <= '"+end+"' AND status = '"+status+"'group by end_date ;");
				while (rs.next())
				{
					System.out.println(rs.getDate(1));
					System.out.println(rs.getString(2));
					ar.add(rs.getString(1));
					ar.add(rs.getString(2));
				}
			
			return ar;
		
		}
		catch (Exception ex )
		{
				ex.printStackTrace();
		}
		return null;
		
	}
	public ArrayList<String> getreportclose(Date start,Date end) {

		ArrayList<String> arr=new ArrayList<String>();
		
			Statement stmt;
			SQLConnection conn = new  SQLConnection();
			try {
				stmt = conn.getConnection().createStatement();
			
			
			ResultSet rs= stmt.executeQuery("SELECT mtl.supplier_orders.order_id,mtl.supplier.name,mtl.supplier_orders.start_date, mtl.supplier_orders.end_date,mtl.supplier_orders.price,mtl.supplier_orders.status FROM mtl.supplier_orders,mtl.supplier where mtl.supplier_orders.supp_id=mtl.supplier.sup_id and status = '"+"closed"+"' AND start_date >= '"+start+"' AND end_date <= '"+end+"';");
				while (rs.next())
				{
					
					arr.add(rs.getString(1));
					arr.add(rs.getString(2));
					arr.add(rs.getString(3));
					arr.add(rs.getString(4));
					arr.add(rs.getString(5));
					arr.add(rs.getString(6));
				}
		
			}
			catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	return arr;
}
	public ArrayList<String> getreportopendb(Date start,Date end) {

		ArrayList<String> arr=new ArrayList<String>();
		
			Statement stmt;
			SQLConnection conn = new  SQLConnection();
			try {
				stmt = conn.getConnection().createStatement();
			
			
			
			ResultSet rs= stmt.executeQuery("SELECT a.order_id,b.name,a.start_date, a.end_date,a.price,a.status FROM mtl.supplier_orders a,mtl.supplier b where a.supp_id=b.sup_id AND a.start_date >= '"+start+"'  and a.status <> '"+"closed"+"';");
			while (rs.next())
				{
					
					arr.add(rs.getString(1));
					arr.add(rs.getString(2));
					arr.add(rs.getString(3));
					arr.add(rs.getString(4));
					arr.add(rs.getString(5));
					arr.add(rs.getString(6));
				}
		
			}
			catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	return arr;
	}
	
	public ArrayList<String> getreportcust(Date start,Date end,String status) throws SQLException
	{
		try 
		{

			Statement stmt;
			Date date1 = null;
			SQLConnection conn = new  SQLConnection();
			stmt = conn.getConnection().createStatement();
			ArrayList<String> ar = new ArrayList<String>();
			ResultSet rs= stmt.executeQuery("SELECT end_date,sum(price) as cnt FROM mtl.customer_orders where Start_date >= '"+start+"' AND end_date  <= '"+end+"' AND status = '"+status+"'group by end_date ;");
				while (rs.next())
				{
					System.out.println(rs.getDate(1));
					System.out.println(rs.getString(2));
					ar.add(rs.getString(1));
					ar.add(rs.getString(2));
				}
			
			return ar;
		
		}
		catch (Exception ex )
		{
				ex.printStackTrace();
		}
		return null;
		
	}
	public ArrayList<String> getreportclosecust(Date start,Date end) {

		ArrayList<String> arr=new ArrayList<String>();
		
			Statement stmt;
			SQLConnection conn = new  SQLConnection();
			try {
				stmt = conn.getConnection().createStatement();
			
			
			ResultSet rs= stmt.executeQuery("SELECT mtl.customer_orders.order_id,mtl.customer.name,mtl.customer_orders.start_date, mtl.customer_orders.end_date,mtl.customer_orders.price,mtl.customer_orders.status FROM mtl.customer_orders,mtl.customer where mtl.customer_orders.cust_id=mtl.customer.cust_id and status = '"+"closed"+"' AND start_date >= '"+start+"' AND end_date <= '"+end+"';");
				while (rs.next())
				{
					
					arr.add(rs.getString(1));
					arr.add(rs.getString(2));
					arr.add(rs.getString(3));
					arr.add(rs.getString(4));
					arr.add(rs.getString(5));
					arr.add(rs.getString(6));
				}
		
			}
			catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	return arr;
	}
	
	public ArrayList<String> getreportopendbcust(Date start,Date end) {

		ArrayList<String> arr=new ArrayList<String>();
		
			Statement stmt;
			SQLConnection conn = new  SQLConnection();
			try {
				stmt = conn.getConnection().createStatement();
			
			
			
			ResultSet rs= stmt.executeQuery("SELECT a.order_id,b.name,a.start_date, a.end_date,a.price,a.status FROM mtl.customer_orders a,mtl.customer b where a.cust_id=b.cust_id AND a.start_date >= '"+start+"'  and a.status <> '"+"closed"+"';");
			while (rs.next())
				{
					
					arr.add(rs.getString(1));
					arr.add(rs.getString(2));
					arr.add(rs.getString(3));
					arr.add(rs.getString(4));
					arr.add(rs.getString(5));
					arr.add(rs.getString(6));
				}
		
			}
			catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	return arr;
	}
	
}
